package com.scrum.Object;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.mysql.jdbc.Statement;
import com.scrum.Connection.DBConnection;

public class Post {
	int id;
	String title;
	int catalog_id;
	String image;
	String description;
	int user_id;
	Date post_time;
	
	
	public Post(){
		
	}
	
	public Post(int id, String title, String image, String description, int catalog_id, int user_id, Date post_time) {
		super();
		this.id = id;
		this.title = title;
		this.catalog_id = catalog_id;
		this.image = image;
		this.description = description;
		this.user_id = user_id;
		this.post_time = post_time;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public int getCatalog_id() {
		return catalog_id;
	}
	public void setCatalog_id(int catalog_id) {
		this.catalog_id = catalog_id;
	}
	public String getImage() {
		return image;
	}
	public void setImage(String image) {
		this.image = image;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public int getUser_id() {
		return user_id;
	}
	public void setUser_id(int user_id) {
		this.user_id = user_id;
	}
	public Date getPost_time() {
		return post_time;
	}
	public void setPost_time(Date post_time) {
		this.post_time = post_time;
	}
	
	
	public static Post addpost(String title, int catalog_id, String image,String description, int user_id) throws ClassNotFoundException, SQLException {
		Post post = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		java.util.Date date = new java.util.Date();
		try {
					String sql = "insert into post(title, image, description, user_id, catalog_id, post_time) values (?,?,?,?,?,?)";
					con = DBConnection.connectDB();
					pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					pstmt.setString(1, title);
					pstmt.setString(2, image);
					pstmt.setString(3, description);
					pstmt.setInt(4, user_id);
					pstmt.setInt(5, catalog_id);
					pstmt.setDate(6, new java.sql.Date(date.getTime()));
					pstmt.executeUpdate();
					rs = pstmt.getGeneratedKeys();
					
					if (rs != null && rs.next()) {
						
					post = new Post(rs.getInt(1), title, image, description, user_id, catalog_id, new java.sql.Date(date.getTime()));
					System.out.println("I don't ");
						System.out.println(post.getTitle());
				}else{
					System.out.println("rs null");
				}
		
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (Exception ex) {
			}
		}
		return post;
	}
	
	public static void deletePost(int id) throws ClassNotFoundException, SQLException{
		Connection con = null;
        PreparedStatement pstmt = null;
        
        String sql = "Delete from post where id = ?";
        try {
            con = DBConnection.connectDB();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
           
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception ex) {
            }
        }
	}
	
	public Post update(String title, int catalog_id, String image,String description) throws ClassNotFoundException, SQLException{
		Connection con = null;
        PreparedStatement pstmt = null;
        java.util.Date date = new java.util.Date();
        String sql = "Update post set title =  ?, catalog_id = ?, image = ?, description = ?, post_time = ? where id = ?";
        try {
            con = DBConnection.connectDB();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, title);
            pstmt.setInt(2, catalog_id);
            pstmt.setString(3, image);
            pstmt.setString(4, description);
            pstmt.setDate(5, new Date(date.getTime()));
            pstmt.setInt(6, id);
            pstmt.executeUpdate();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception ex) {
            }
        }
		this.setCatalog_id(catalog_id);
		this.setTitle(title);
		this.setDescription(description);
		this.setImage(image);
		return this;
	}
	
	
	public static Vector<Post> getAllPost() throws SQLException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<Post> postarray = new Vector<Post>();
		try {
			String sql = "SELECT * FROM post";
			con = DBConnection.connectDB();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				postarray.add(new Post(rs.getInt("id"), rs.getString("title"), rs.getString("image"), rs.getString("description"), rs.getInt("catalog_id"), rs.getInt("user_id"), rs.getDate("post_time")));
			}
		}
		finally {
			rs.close();
			pstmt.close();
			con.close();
		}
		System.out.println(postarray.size());
		return postarray;
	}
	
	public static Vector<Post> getAllPostByUserId(int user_id) throws SQLException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<Post> postarray = new Vector<Post>();
		try {
			String sql = "SELECT * FROM post where user_id = "+user_id;
			con = DBConnection.connectDB();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				postarray.add(new Post(rs.getInt("id"), rs.getString("title"), rs.getString("image"), rs.getString("description"), rs.getInt("catalog_id"), rs.getInt("user_id"), rs.getDate("post_time")));
			}
		}
		finally {
			rs.close();
			pstmt.close();
			con.close();
		}
		System.out.println(postarray.size());
		return postarray;
	}
	
	public static Vector<Post> getAllPostByTitleUserId(int user_id, String title) throws SQLException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<Post> postarray = new Vector<Post>();
		try {
			String sql = "SELECT * FROM post where title like ? AND user_id = ?";
			con = DBConnection.connectDB();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%"+title+"%");
			pstmt.setInt(2, user_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				postarray.add(new Post(rs.getInt("id"), rs.getString("title"), rs.getString("image"), rs.getString("description"), rs.getInt("catalog_id"), rs.getInt("user_id"), rs.getDate("post_time")));
			}
		}
		finally {
			rs.close();
			pstmt.close();
			con.close();
		}
		System.out.println(postarray.size());
		return postarray;
	}
	
	public static Vector<Post> getUserByTitle(String title) throws ClassNotFoundException, SQLException{
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	Vector<Post> posts = new Vector<Post>();
	try {
		String sql = "SELECT * FROM post where title like ?";
		con = DBConnection.connectDB();
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, "%"+title+"%");
		rs = pstmt.executeQuery();
		while (rs.next()) {
			posts.add(new Post(rs.getInt("id"), rs.getString("title"), rs.getString("image"), rs.getString("description"), rs.getInt("catalog_id"), rs.getInt("user_id"), rs.getDate("post_time")));
		}
	}
	finally {
		rs.close();
		pstmt.close();
		con.close();
	}
	return posts;
	
}
	public static Post getPostById(int id) throws ClassNotFoundException, SQLException{
		Post post = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM post where id = ?";
            con = DBConnection.connectDB();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
            	post =new Post(rs.getInt("id"), rs.getString("title"), rs.getString("image"), rs.getString("description"), rs.getInt("catalog_id"), rs.getInt("user_id"), rs.getDate("post_time"));
            }
        }
        finally {
                           rs.close();
                pstmt.close();
                con.close();
        }
        return post;
	}
	
	
}	
